

-- Ref: http://stackoverflow.com/questions/840831/how-can-i-execute-a-set-of-sql-files-from-within-ssms
--		https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/02/23/sql-server-management-studio-hacking-sqlcmd-mode/
--		http://www.sqlserverspecialists.com/2012/10/sqlcmd-command-line-option.html
--		https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/
--		http://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query

/* Explain: 
While SQLCMD.exe is the best way, SSMS also has a SQLCMD mode where you can execute a SQLCMD script. To enable this mode 
click Query in menu bar then select SQLCMD Mode.

--The ":r filename.sql" command is the SQLCMD script command to import and execute a sql script file. You know you are in 
SQLCMD mode because any lines that are SQLCMD script commands will appear with colored (gray I think) background.

--:setvar path "c:\Path_to_scripts\"
--:r $(path)\file1.sql
--:r $(path)\file2.sql

Example:
--------

--:setvar MyDir "C:\scripts"
--:OUT $(MyDir)\test.txt
--PRINT ':SETVAR ServerName ''' + @@SERVERNAME + ''''
--GO
--:OUT stdout
--:r $(MyDir)\test.txt
--GO
--SELECT $(ServerName)

Example #02:
------------

--:OUT $(TEMP)\GetServerName.sql
--PRINT ':SETVAR FilePath $(TEMP)\' + @@SERVERNAME + '.txt'
--GO
--:OUT stdout
--:r $(TEMP)\GetServerName.sql
--GO
--:OUT $(FilePath)
--SELECT name, create_date 
--FROM master.sys.server_principals
--GO
--:OUT stdout
--!!notepad $(TEMP)\GetServerName.sql
--!!notepad $(FilePath)

Invoke Path: ToolBar --> Query --> SQLCMD Mode

*/

USE [master];
GO

-- This script path is for Gokul
:setvar tSQLScriptPath "D:\Projects\Developer Series\developer.demoProjects\iApprove\iApprove.SQL"

--This script path is for Iswarya (please comment the above line and uncomment the below line)
--:setvar tSQLScript "c:\Path_to_scripts"

--01. Database Creation

:r $(tSQLScriptPath)\01.tSQL_CREATE_Database.sql
GO

--02. Table Creation

:r $(tSQLScriptPath)\30.01.tSQL_TABLE_User.sql
GO

:r $(tSQLScriptPath)\30.02.tSQL_TABLE_Category.sql
GO

:r $(tSQLScriptPath)\30.03.tSQL_TABLE_StatusCode.sql
GO

:r $(tSQLScriptPath)\30.04.tSQL_TABLE_UserMaster.sql
GO

:r $(tSQLScriptPath)\30.05.tSQL_TABLE_Ticket.sql
GO

:r $(tSQLScriptPath)\30.06.tSQL_TABLE_TicketStatus.sql
GO

--03. Stored Procedure Creation

:r $(tSQLScriptPath)\35.sProc_GET_Category.sql
GO

:r $(tSQLScriptPath)\35.sProc_INSERT_Ticket.sql
GO

--04. Master Data Insert Statements

:r $(tSQLScriptPath)\80.01.tSQL_INSERT_Mst_User.sql
GO

:r $(tSQLScriptPath)\80.02.tSQL_INSERT_Mst_Category.sql
GO

:r $(tSQLScriptPath)\80.03.tSQL_INSERT_Mst_StatusCode.sql
GO

:r $(tSQLScriptPath)\80.03.tSQL_INSERT_Mst_StatusCode.sql
GO

--05. Transaction Data Insert Statements

:r $(tSQLScriptPath)\81.01.tSQL_INSERT_Tran_UserMaster.sql
GO

/* OP:
Database : SGLDBMP01, created successfully with CompMode: 100 and RecMode: Simple
Table : User, created successfully
Table : Category, created successfully
Table : StatusCode, created successfully
Table : UserMaster, created successfully
Table : Ticket, created successfully
Table : TicketStatus, created successfully
Stored Procedure: 'dbo.sProc_GET_Category', created successfully
Stored Procedure: 'dbo.sProc_INSERT_Ticket', created successfully

OP: Database : SGLDBMP01, already exists in this instance, please create with different name
Table : User, with the name already exists in the database.
Table : Category, with the name already exists in the database.
Table : StatusCode, with the name already exists in the database.
Table : UserMaster, with the name already exists in the database.
Table : Ticket, with the name already exists in the database.
Table : TicketStatus, with the name already exists in the database.
Stored Procedure: 'dbo.sProc_GET_Category', created successfully
Stored Procedure: 'dbo.sProc_INSERT_Ticket', created successfully

(7 row(s) affected)

(11 row(s) affected)

OP#02: 
------
Msg 911, Level 16, State 1, Line 67
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 72
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 70
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 73
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 69
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 69
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 70
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Stored Procedure: 'dbo.sProc_GET_Category', created successfully
Msg 911, Level 16, State 1, Line 1
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Stored Procedure: 'dbo.sProc_INSERT_Ticket', created successfully
Msg 911, Level 16, State 1, Line 10
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 36
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 36
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 32
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 35
Database 'SGLDBMP01' does not exist. Make sure that the name is entered correctly.
Msg 208, Level 16, State 1, Line 16
Invalid object name 'dbo.UserMaster'.


OP#03: 
------
A fatal scripting error occurred.
Variable tSQLScriptPath is not defined.

*/